package com.ysd.Dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Design;
import com.ysd.entity.ProductSeries;
import com.ysd.util.DBmanager;

/**
 * 查询所有产品类别
 * @author 86176
 *
 */
public class ProductSeriesDaoImpl {
	public List<ProductSeries> getProductSeriesList(){
		Connection conn=null;
		Statement st = null;
		ResultSet rs = null;
		List<ProductSeries> list = new ArrayList<ProductSeries>();
		conn = DBmanager.getConnection();
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from productseries");
			while(rs.next()) {
				int psId = rs.getInt("productSeriesId");
				String psName = rs.getString("productSeriesName");
				ProductSeries ps = new ProductSeries(psId,psName);
			list.add(ps);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBmanager.closeAll(conn, st, rs);
		}
		
	}

	/**
	 * 分页查询
	 * @return
	 */
		public List<ProductSeries> getProductCategoryByPage(int page,int size){
			Connection conn=null;
			Statement st = null;
			ResultSet rs = null;
			List<ProductSeries> list = new ArrayList<ProductSeries>();
			conn = DBmanager.getConnection();
			try {
				st = conn.createStatement();
				String sql = "select * from productseries order by productSeriesId desc limit "+(page-1)*size+","+size;
				System.out.println(sql);
				rs = st.executeQuery(sql);
				while(rs.next()) {
					int psId = rs.getInt("productSeriesId");
					String psName = rs.getString("productSeriesName");
					ProductSeries ps = new ProductSeries(psId,psName);
				list.add(ps);
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}finally {
				DBmanager.closeAll(conn, st, rs);
			}
			
		}
		
	/**
	 * 通过系列id获取系列数据
	 * @param pssId
	 * @return
	 */
	public ProductSeries getProductSeriesList(int pssId){
		Connection conn=null;
		Statement st = null;
		ResultSet rs = null;
		ProductSeries ps = null;
		conn = DBmanager.getConnection();
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from productseries where productSeriesId="+pssId);
			while(rs.next()) {
				int psId = rs.getInt("productSeriesId");
				String psName = rs.getString("productSeriesName");
				ps = new ProductSeries(psId,psName);
			}
			return ps;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBmanager.closeAll(conn, st, rs);
		}
		
	}
	
	/**
	 * 
	 * 根据产品分类id删除分类
	 * pId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int deleteProductCategoryById(int pId) {
		return DBmanager.executeUpdate("DELETE  FROM productseries where productSeriesId ="+pId);
	}
	
	/**
	 * 
	 * 批量根据产品分类id删除分类
	 * pId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int deleteProductCategoryByIds(String pIds) {
		String sql ="DELETE FROM productseries where productSeriesId in("+pIds+")";
		System.out.println(sql);
		return DBmanager.executeUpdate(sql);
	}
	/**
	   *添加产品分类
	 * ps 前台传来产品分类名称
	 *return 受影响行数
	 */
	public int addProductCategory(ProductSeries ps) {
		return DBmanager.executeUpdate("insert into productseries VALUES(null,'"+ps.getProductSeriesName()+"')");
	}
	
	/**
	 * 通过产品分类名称查询产品分类名称是否存在
	 * String name 接收前台输入的分类名称
	 * @param name
	 * @return
	 */
	public ProductSeries getProductSeriesByName(String name){
		Connection conn=null;
		Statement st = null;
		ResultSet rs = null;
		ProductSeries ps=null;
		conn = DBmanager.getConnection();
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from productseries where productSeriesName='"+name+"' ");
			while(rs.next()) {
				int psId = rs.getInt("productSeriesId");
				String psName = rs.getString("productSeriesName");
				 ps = new ProductSeries(psId,psName);
			}
			return ps;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBmanager.closeAll(conn, st, rs);
		}
		
	}
	/**
	   *编辑产品分类
	 * ps 前台传来产品分类数据
	 *return 受影响行数
	 */
	public int editProductCategory(ProductSeries ps) {
		return DBmanager.executeUpdate("update productseries set productSeriesName = '"+ps.getProductSeriesName()+"'  where productSeriesId = '"+ps.getProductSeriesId()+"' ");
	}
}
